﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using NHTU;

namespace FinalSettlementModel
{
    public class Helper
    {
        public static NOMFinalSettlementDataContext nc;

        SqlConnectionStringBuilder sqlBuilder = SQLServer.ConnectionStringDeCrypt(Properties.Settings.Default.ConnectionString);

        string connString = "";

        public SqlConnection conn = null;
        
        public delegate void LoadData();

        public static string Message { set; get; }
        public event LoadData EventLoadData;

        /// <summary>
        /// Constructor
        /// </summary>
        public Helper()
        {
            connString = sqlBuilder.ConnectionString;
            try
            {
                nc = new NOMFinalSettlementDataContext(connString);
                nc.Connection.Open();
                // Stop an existing services on this connection string
                SqlDependency.Stop(connString);

                // Start the dependency
                // User must have SUBSCRIBE QUERY NOTIFICATIONS permission
                // Database must also have SSB enabled
                // ALTER DATABASE NOMFinalSettlement SET ENABLE_BROKER
                SqlDependency.Start(connString);

                conn = new SqlConnection(connString);
            }
            catch
            {
                MsgBox.Show("Không có kết nối dữ liệu. Vui lòng kiểm tra lại cấu hình.", "Thông báo", MsgBoxButton.Ok, MsgBoxIcon.Warning);
            }
            
        }

        /// <summary>
        /// Destructor
        /// </summary>
        ~Helper()
        {
            try
            {
                // Stop the dependency before exiting
                SqlDependency.Stop(connString);
            }
            catch { }
        }

        /// <summary>
        /// Handler for the SqlDependency OnChange event
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;

            // Notices are only a one shot deal
            // so remove the existing one so a new 
            // one can be added
            dependency.OnChange -= OnChange;

            // Fire the event
            if (EventLoadData != null)
            {
                EventLoadData();
            }
        }

        /// <summary>
        /// Execute DataTable from database
        /// </summary>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string Query, SqlParameter[] Parameters)
        {
            DataTable dt = new DataTable();

            try
            {

                SqlCommand cmd = new SqlCommand(Query, conn);
                if (Parameters != null)
                {
                    foreach (SqlParameter p in Parameters)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                cmd.CommandType = CommandType.Text;

                // Clear any existing notifications
                cmd.Notification = null;

                // Create the dependency for this command
                SqlDependency dependency = new SqlDependency(cmd);

                // Add the event handler
                dependency.OnChange += new OnChangeEventHandler(OnChange);

                // Open the connection if necessary
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                // Get the messages
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));

                // Clear Parameters
                if (Parameters != null)
                {
                    cmd.Parameters.Clear();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                Message = ex.ToString() ;
            }
            finally
            {
                SqlConnection.ClearAllPools();
            }

            return dt;

        }

        


        /// <summary>
        /// Get database Object
        /// </summary>
        /// <returns>Object</returns>
        public Object ExecuteScalarObject(string Query, SqlParameter[] Parameters)
        {
            Object obj;

            try
            {

                SqlCommand cmd = new SqlCommand(Query, conn);
                if (Parameters != null)
                {
                    foreach (SqlParameter p in Parameters)
                    {
                        cmd.Parameters.Add(p);
                    }
                }

                // Open the connection if necessary
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                // Get the messages
                obj = cmd.ExecuteScalar();

                // Clear Parameters
                if (Parameters != null)
                {
                    cmd.Parameters.Clear();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return obj;

        }
    }
}
